package com.nwpu.hass.serviceimpl;

import com.nwpu.hass.domain.DeviceEntity;
import com.nwpu.hass.domain.JsonResponse.*;
import com.nwpu.hass.domain.RegisterformEntity;
import com.nwpu.hass.domain.UserEntity;
import com.nwpu.hass.finals.FinalData;
import com.nwpu.hass.repository.RegisterformRepository;
import com.nwpu.hass.repository.DeviceRepository;
import com.nwpu.hass.repository.UserRepository;
import com.nwpu.hass.service.DeviceService;
import com.nwpu.hass.service.MessageService;
import com.nwpu.hass.utils.*;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.openxmlformats.schemas.presentationml.x2006.main.STName;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;

import org.springframework.beans.factory.annotation.Autowired;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.util.*;

@Slf4j
@Service
public class DeviceServiceImpl implements DeviceService {

    @Resource
    DeviceRepository deviceRepository;
    @Resource
    RegisterformRepository registerformRepository;
    @Resource
    UserRepository userRepository;
    @Resource
    FileComponent fileComponent;
    @Resource
    MessageService messageService;
    @Autowired
    EmailSender emailSender;
    @Autowired
    private EntityManager entityManager;

    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd_HH_mm_ss");

    @Override
    public DeviceEntity idleDevice(String type, String family, String deviceName, Long userId) {

        List<DeviceEntity> freeDevices = deviceRepository.findAllByTypeAndFamilyAndDeviceNameOrderByUsedTimes(type, family, deviceName);
        DeviceEntity idleDevice;
        // 根据type和family没有找到空闲设备
        if (Objects.isNull(freeDevices) || freeDevices.isEmpty()) {
            idleDevice = null;
        } else {
            RegisterformEntity registerformEntity = new RegisterformEntity();
            idleDevice = freeDevices.get(0);
            idleDevice.setStatus(Status.ONBORROW.getStatus());
            deviceRepository.save(idleDevice);
            registerformEntity.setBorrowTime(new Timestamp(System.currentTimeMillis()));
            registerformEntity.setBorrowPerson(userId);
            registerformEntity.setDevice(idleDevice.getDeviceId());
            registerformEntity.setRecord(new Timestamp(System.currentTimeMillis()));
            registerformRepository.save(registerformEntity);

            String msgContent = "有人在借" + idleDevice.getType() + idleDevice.getFamily() + "，快来审核吧~~~";
            messageService.addSystemMessage(msgContent);

        }
        return idleDevice;
    }

    @Override
    public DeviceEntity returnDevice(Long userId,Long returnId) {
        Optional<DeviceEntity> deviceOptional;
        deviceOptional = deviceRepository.findById(returnId);
        if (deviceOptional.isEmpty()) {
            return null;
        }
        DeviceEntity deviceEntity = deviceOptional.get();
        if (deviceEntity.getStatus() != Status.BUSY.getStatus()) {
            return null;
        }
        // 归还了设备,修改了设备状态
        deviceEntity.setStatus(Status.ONRETURN.getStatus());
        deviceRepository.save(deviceEntity);
        // registerform中需要修改return time
        RegisterformEntity registerformEntity = registerformRepository.findRegisterformEntityByBorrowPersonAndDeviceOrderByRecordDesc(userId,returnId);
        registerformEntity.setReturnTime(Tools.timestamp());
        registerformRepository.save(registerformEntity);

        String msgContent = "有人想还" + deviceEntity.getType() + deviceEntity.getFamily() + "，快来审核吧~~~";
        messageService.addSystemMessage(msgContent);

        return deviceEntity;
    }

    @Override
    public List<ApplyEntity> useChecksReturnList(Integer pageNo) {
        List<DeviceEntity> returningDevices;
        List<ApplyEntity> applies = new ArrayList<>();
        returningDevices = deviceRepository.findReturning(pageNo*7-7,7);

        UserEntity borrower;
        RegisterformEntity registerformEntity;
        for (DeviceEntity entity : returningDevices) {
            registerformEntity = registerformRepository.findByDeviceId(entity.getDeviceId()).get(0);
            long borrowerId = registerformEntity.getBorrowPerson();
            borrower = userRepository.findUserEntityByUserid(borrowerId);
            Timestamp applyTime = registerformEntity.getReturnTime();
            applies.add(new ApplyEntity(borrower, applyTime, entity,registerformEntity.getRegisterid()));
        }
        return applies;
    }

    @Override
    public List<ApplyEntity> useChecksBorrowList(Integer pageNo) {
        List<DeviceEntity> borrowingDevices;
        List<ApplyEntity> applies = new ArrayList<>();
        borrowingDevices = deviceRepository.findBorrowing(pageNo*7-7,7);

        UserEntity borrower;
        RegisterformEntity registerformEntity;

        for(DeviceEntity entity : borrowingDevices) {
            registerformEntity = registerformRepository.findByDeviceId(entity.getDeviceId()).get(0);
            long borrowerId = registerformEntity.getBorrowPerson();
            borrower = userRepository.findUserEntityByUserid(borrowerId);
            Timestamp applyTime = registerformEntity.getBorrowTime();
            applies.add(new ApplyEntity(borrower, applyTime, entity,registerformEntity.getRegisterid()));
        }
        return applies;
    }

    @Override
    public void updateDevice(DeviceEntity deviceEntity) {
        DeviceEntity deviceExist = deviceRepository.findDeviceEntityByDeviceId(deviceEntity.getDeviceId());
        if (!Objects.isNull(deviceExist)) {
            deviceRepository.save(deviceEntity);
        }
    }

    @Override
    public DeviceEntity getDeviceById(Long deviceId) {
        return deviceRepository.findDeviceEntityByDeviceId(deviceId);
    }

    @Override
    public List<DeviceEntity> getAllDevice(int pageNo) {
        return deviceRepository.findAllDevices(pageNo*7-7,7);
    }

    @Override
    public List<DeviceEntity> checkInDevices(long userId, MultipartFile file) {

        log.info("运行<checkInDevices>方法...");
        List<DeviceEntity> deviceEntities = new ArrayList<>();
        String fileName = userId + "_" + sdf.format(new Date());
        try {
            log.info("try-catch");
            File tempFile = fileComponent.createFile( fileName, "xlsx");
            log.info("file name => " + fileName);
            log.info("Absolute Path => " + tempFile.getAbsolutePath());
            try {
                if(!tempFile.exists()) {
                    log.info(String.valueOf(tempFile.createNewFile()));
                }
            }catch (Exception e){
                log.error(e.getMessage());
            }

            FileOutputStream fos = new FileOutputStream(tempFile);
            log.info("FileOutputStream => " + fos.toString());
            fos.write(file.getBytes());
            log.info("file bytes => " + file.getBytes().toString());
            fos.close();
            log.info("transfer successful");
            Workbook workbook = WorkbookFactory.create(tempFile);
            log.info("create successful");
            //获取一张表
            Sheet sheet = workbook.getSheetAt(0);
            long num = sheet.getLastRowNum();
            log.info("共上传" + num + "条数据");
            for (int i = 1; i < num; i++) {
                log.info("第 " + i + " 条数据已读入");
                Row row = sheet.getRow(i);
                DeviceEntity deviceEntity = new DeviceEntity();
                String[] str = new String[row.getLastCellNum()];
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    Cell cell = row.getCell(j);
                    if(Objects.isNull(cell)){
                        continue;
                    }
                    cell.setCellType(CellType.STRING);
                    str[j] = cell.getStringCellValue().trim();
                }
                if(null == str[0]){
                    break;
                }
                deviceEntity.setType(str[0]);
                deviceEntity.setFamily(str[1]);
                deviceEntity.setDeviceName(str[2]);
                deviceEntity.setRecord(new Timestamp(System.currentTimeMillis()));
                deviceEntity.setUsedYear(Integer.parseInt(str[3]));
                deviceEntity.setCost(Double.parseDouble(str[5]));
                deviceEntity.setStatus(0);
                deviceEntity.setUsedTimes(0);
                deviceEntity.setDevicePic(str[8]);
                deviceEntities.add(deviceEntity);
            }
        } catch (IOException | InvalidFormatException e) {

        }
        return deviceRepository.saveAll(deviceEntities);
    }

    @Override
    public List<IdleDevicesGroupResponse> getAllIdleDeviceGroup(int min,int max) {
        /*
         * JPQL貌似不支持limit函数，这里使用native query
         */
        Query query = entityManager.createNativeQuery("select type,family,deviceName,devicePic,count(*)" +
                "from device where status=0 group by type, family, deviceName,devicePic"+" limit ?1,?2");
        query.setParameter(1,min);
        query.setParameter(2,max);
        List result = query.getResultList();
        List<IdleDevicesGroupResponse> responses = new ArrayList<>();
        if (result != null) {
            Iterator iterator = result.iterator();
            while (iterator.hasNext()) {
                IdleDevicesGroupResponse temp = new IdleDevicesGroupResponse();
                Object[] row = (Object[]) iterator.next();
                temp.setType(row[0].toString());
                temp.setFamily(row[1].toString());
                temp.setDeviceName(row[2].toString());
                temp.setUrl(row[3].toString());
                temp.setCount(Integer.parseInt(row[4].toString()));

                responses.add(temp);
            }
        }
        return responses;
    }

    @Override
    public List<DeviceEntity> getAllBorrowedDevice(Long userId,Integer pageNo) {
        Query query = entityManager.createNativeQuery("select * from borrowedItem where status=2 and userid=?1 limit ?2,?3");
        query.setParameter(1,userId);
        query.setParameter(2,pageNo*7-7);
        query.setParameter(3,7);
        List rows = query.getResultList();
        List<DeviceEntity> deviceEntities = new ArrayList<>();
        if(rows!=null){
            Iterator iterator = rows.iterator();
            while(iterator.hasNext()){
                DeviceEntity deviceEntity = new DeviceEntity();
                Object[] row = (Object[])iterator.next();
                deviceEntity.setDeviceId(Long.parseLong(row[1].toString()));
                deviceEntity.setType((String) row[2]);
                deviceEntity.setFamily((String) row[3]);
                deviceEntity.setDeviceName((String) row[4]);
                deviceEntity.setUsedYear((Integer) row[5]);
                deviceEntity.setPurchasedTime((Timestamp) row[6]);
                deviceEntity.setCost(Double.valueOf(row[7].toString()));
                deviceEntity.setStatus((Integer) row[8]);
                deviceEntity.setUsedTimes((Integer) row[9]);
                deviceEntity.setDevicePic(row[10].toString());
                deviceEntity.setRecord((Timestamp) row[11]);
                deviceEntity.setPosition((String) row[12]);
                deviceEntities.add(deviceEntity);
            }
        }
        return deviceEntities;
    }

    @Override
    public DeviceEntity getOneDeviceInfo(long deviceId) {

        Optional<DeviceEntity> deviceEntityOptional = deviceRepository.findById(deviceId);
        return deviceEntityOptional.orElse(null);
    }

    @Override
    public List<DeviceEntity> getNoPosDevices(int pageNo) {

        return deviceRepository.findAllByPositionIsNull(pageNo*7-7,7);

    }

    /**
     * 若有多个id，请以如下格式输入，
     * id1,id2,id3
     * @param deviceIds 所有报修设备的id
     */
    @Async
    @Override
    public void sendMailAndWeChat(String deviceIds) throws Exception {
        //仓库管理员的role为2
        List<UserEntity> userList = userRepository.findUserEntityByMrole(2);
        for(UserEntity user:userList){
            emailSender.sendTxtMail(user.getEmail(),deviceIds);
            emailSender.sendWechatMail(user.getWechatAccount(),deviceIds);
        }
    }

    @Override
    public List<DevicesByStatusResponse> getDeviceGroupByStatus() {
        Query query = entityManager.createQuery(
                "select device.status,count(device) from  DeviceEntity device" +
                        " group by device.status");
        List result = query.getResultList();
        Map<Integer,Long> map = new LinkedHashMap<>();
        /*
         * 查询的结果可能不包含所有的设备状态
         */
        for(Map.Entry<Integer,String> entry: FinalData.STATUS_MAP.entrySet()){
            map.put(entry.getKey(),0L);
        }
        if(result != null){
            for(Object object: result){
                Object[] row = (Object[]) object;
                map.put((Integer) row[0],Long.parseLong(row[1].toString()));
            }
        }
        List<DevicesByStatusResponse> responses = new ArrayList<>();
        for(Map.Entry<Integer,Long> entry: map.entrySet()){
            String status = FinalData.STATUS_MAP.get(entry.getKey());
            DevicesByStatusResponse response = new DevicesByStatusResponse(status,entry.getValue());
            responses.add(response);
        }
        return responses;
    }

    @Override
    public List<DevicesByDepartmentResponse> getDeviceGroupByDepartment() {
        Query query = entityManager.createQuery(
                "select device.position,count(device) from  DeviceEntity device" +
                        " group by device.position");
        List result = query.getResultList();
        Map<String,Long> map = new HashMap<>();
        Iterator<String> iterator = FinalData.DEPARTMENTS.iterator();
        while(iterator.hasNext()){
            map.put(iterator.next(),0L);
        }
        if(result!=null){
            for(Object rows: result){
                Object[] row= (Object[]) rows;
                Long count = Long.parseLong(row[1].toString());
                if(Objects.isNull(row[0])){
                    map.put("未入库",count);
                }else{
                    map.put(row[0].toString(),count);
                }
            }
        }
        List<DevicesByDepartmentResponse> responses = new ArrayList<>();
        for(Map.Entry<String,Long> entry:map.entrySet()){
            DevicesByDepartmentResponse response = new DevicesByDepartmentResponse(entry.getKey(),entry.getValue());
            responses.add(response);
        }
        return responses;
    }

    @Override
    public List<DeviceEntity> getOnDevices(Long userId,int status) {
        // 用户借过的设备对应的id
        Query query = entityManager.createQuery("select register.device from RegisterformEntity register " +
                " where register.borrowPerson=?1 group by register.device" );
        query.setParameter(1,userId);
        List result = query.getResultList();
        List<Long> deviceIds = new ArrayList<>();
        List<DeviceEntity> responses = new ArrayList<>();
        if(result!=null){
            for(Object object: result){
                //Object[] row = (Object[]) object;
                // 用户结果设备的id集合
                deviceIds.add(Long.parseLong(object.toString()));
            }
            for(Long deviceId:deviceIds){
                DeviceEntity deviceEntity = deviceRepository.findByDeviceIdAndStatus(deviceId,status);
                if(!Objects.isNull(deviceEntity)){
                    RegisterformEntity registerformEntity = registerformRepository.findAllByDeviceOrderByRecordDesc(deviceId).get(0);
                    if(registerformEntity.getBorrowPerson()==userId){
                        responses.add(deviceEntity);
                    }
                }
            }
        }
        return responses;
    }

    @Override
    public List<DeviceEntity> getDevicesByFamily(String family) {
        return deviceRepository.findAllByFamily(family);
    }

    @Override
    public List<DeviceEntity> getDevicesByStatus(int status) {
        return deviceRepository.findAllByStatus(status);
    }

    @Override
    public List<DeviceEntity> getDevicesByStatusPageNo(int pageNo) {
        return deviceRepository.findPageableOnScrapedDevices(pageNo*7-7,7);
    }
}
